<?php

$dfr = $_GET['dfr'];
$dto = $_GET['dto'];
$filter = $_GET['filter'];

function xlsBOF() {
    echo pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);  
    return;
}

function xlsEOF() {
    echo pack("ss", 0x0A, 0x00);
    return;
}

function xlsWriteNumber($Row, $Col, $Value) {
    echo pack("sssss", 0x203, 14, $Row, $Col, 0x0);
    echo pack("d", $Value);
    return;
}

function xlsWriteLabel($Row, $Col, $Value ) {
    $L = strlen($Value);
    echo pack("ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L);
    echo $Value;
return;
}

require_once("conf/db_connection.php");

	$filename = "DAILY_PICKUP_REQUIREMENT_REPORT".date('Y-m-d');
    // Send Header
    header("Pragma: public");
    header("Expires: 0");
    header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
    header("Content-Type: application/force-download");
    header("Content-Type: application/octet-stream");
    header("Content-Type: application/download");;
    header("Content-Disposition: attachment;filename=$filename.xls ");
    header("Content-Transfer-Encoding: binary ");

	
	
   // Query Database
   if($filter==0)
   $stat = 'complete';
   if($filter==1)
   $stat = 'cancelled';
   if($filter==2)
   $stat = 'rejected';
   
   //QUERY1
   /*$query_db = "SELECT trip_detail.*,status_log.*,trip_detail.truck_id as tid FROM trip_detail JOIN status_log ON
   trip_detail.id = status_log.trip_detail_id WHERE trip_detail.status='complete' AND 
   (status_log.update_date>='".$dfr."' AND status_log.update_date<='".$dto."') AND 
   status_log.status LIKE '%".$stat."%'";*/
   
   //QUERY2
   if($stat=='complete' || $stat=='rejected')
   {
   $query_report = "SELECT status_log.*,trip_detail.*,trip_detail.truck_id as tid,status_log.truck_id as status_tid,status_log.status as statlog,trip_detail.id as trid FROM status_log JOIN trip_detail ON status_log.trip_detail_id=trip_detail.id WHERE status_log.status LIKE '%".$stat."%'
   AND (status_log.update_date>='".$dfr."' AND status_log.update_date<='".$dto."')";
   $result = mysql_query($query_report) or die(mysql_error());
   }
   
   if($stat=='cancelled')
   {
   /*$query_report = "SELECT status_log.*,trip_detail.*,trip_detail.truck_id as tid,status_log.status as statlog,trip_detail.id as trid FROM status_log JOIN trip_detail ON status_log.trip_detail_id=trip_detail.id WHERE trip_detail.is_cancelled='1'
   AND (status_log.update_date>='".$dfr."' AND status_log.update_date<='".$dto."')";*/
   $query_report = "SELECT * FROM trip_detail where is_cancelled='1' AND
   (positioning_date>='".$dfr."' AND positioning_date<='".$dto."')";
   $result = mysql_query($query_report) or die(mysql_error());
   }
   
   //die($query_db);
   //(trip_detail.completion_date>='".$dfr."' AND trip_detail.completion_date<='".$dto."')(status_log.update_date>='".$dfr."' AND status_log.update_date<='".$dto."')
    // XLS Data Cell
	
	
	
	//STATUS COMPLETE
	
			if($stat=='complete')
				{
				xlsBOF();
				//xlsWriteLabel(0,0,"DAILY PICKUP REQUIREMENT REPORT".$dfr.'-'.$dto);
				xlsWriteLabel(0,0,"Booking Number");
				xlsWriteLabel(0,1,"Shipper Code");
                xlsWriteLabel(0,2,"Shipper Name");
				xlsWriteLabel(0,3,"Quantity");
				xlsWriteLabel(0,4,"Container Size");
				xlsWriteLabel(0,5,"Type");
				xlsWriteLabel(0,6,"Commodity Class");
				xlsWriteLabel(0,7,"Trucker Name");
				xlsWriteLabel(0,8,"Pick Up Datetime");
				xlsWriteLabel(0,9,"MT Release Datetime");
				xlsWriteLabel(0,10,"Pick Up Address");
				xlsWriteLabel(0,11,"Contact Person");
				xlsWriteLabel(0,12,"Contact Number");
				xlsWriteLabel(0,13,"Special Instruction");
				xlsWriteLabel(0,14,"Vessel Voyage");
				xlsWriteLabel(0,15,"Vessel Voyage ETD");
                xlsWriteLabel(0,16,"TAS#");
				xlsWriteLabel(0,17,"TAS Status");
				xlsWriteLabel(0,18,"Completion Date");
				xlsWriteLabel(0,19,"Plate#");
				
                $xlsRow = 1;
                while($row=mysql_fetch_array($result)) 
				{
				$query_trip = "SELECT * FROM truck WHERE id='".$row['tid']."'";
				$result_trip = mysql_query($query_trip) or die(mysql_error());
				$row_trip = mysql_fetch_array($result_trip);
				
				$query_trucker = "SELECT * FROM trucker WHERE id='".$row['trucker_id']."'";
				$result_trucker = mysql_query($query_trucker) or die(mysql_error());
				$row_trucker = mysql_fetch_array($result_trucker);
				
				$query_release = "SELECT * FROM status_log WHERE trip_detail_id='".$row['trid']."' AND status='pickup'";
				$result_release = mysql_query($query_release) or die(mysql_error());
				$row_release = mysql_fetch_array($result_release);
				
				if($row['statlog']=='pickup')
				$mtrelease = $row['update_date'];
				
				if($row['statlog']!='pickup')
				$mtrelease = '';
				
				
				
				/*$query_stat = "SELECT * FROM status_log WHERE trip_detail_id='".$row['id']."' and status='complete'";
				$result_stat = mysql_query($query_stat) or die("ERROR");
				$row_stat = mysql_fetch_array($result_stat);*/
				
				++$i;
						  xlsWriteLabel($xlsRow,0,$row['booking_number']);
						  xlsWriteLabel($xlsRow,1,$row['shipper_code']);
						  xlsWriteLabel($xlsRow,2,$row['customer']);
						  xlsWriteLabel($xlsRow,3,$row['quantity']);
						  xlsWriteLabel($xlsRow,4,$row['container_size']);
                          xlsWriteLabel($xlsRow,5,$row['type']);
                          xlsWriteLabel($xlsRow,6,$row['commodity_type']);
						  xlsWriteLabel($xlsRow,7,$row_trucker['name']);
						  xlsWriteLabel($xlsRow,8,$row['positioning_date'].''.$row['positioning_time']);
						  xlsWriteLabel($xlsRow,9,$row_release['update_date']);
						  xlsWriteLabel($xlsRow,10,$row['pick_address']);
						  xlsWriteLabel($xlsRow,11,$row['contact_person']);
						  xlsWriteLabel($xlsRow,12,$row['contact_number']);
						  xlsWriteLabel($xlsRow,13,$row['instruction']);
						  xlsWriteLabel($xlsRow,14,$row['vessel_voyage']);
						  xlsWriteLabel($xlsRow,15,$row['vessel_voyage_etd']);
                          xlsWriteLabel($xlsRow,16,$row['tas_number']);
						  xlsWriteLabel($xlsRow,17,$row['status']);
						  xlsWriteLabel($xlsRow,18,$row['update_date']);
                          xlsWriteLabel($xlsRow,19,$row_trip['plate']);
                    $xlsRow++;
                    
                }
                     xlsEOF();
                 exit();
				}
				
	//STATUS REJECTED
	
			if($stat=='rejected')
				{
				xlsBOF();
				//xlsWriteLabel(0,0,"DAILY PICKUP REQUIREMENT REPORT".$dfr.'-'.$dto);
				xlsWriteLabel(0,0,"Booking Source");
				xlsWriteLabel(0,1,"Booking Number");
				xlsWriteLabel(0,2,"Quantity");
				xlsWriteLabel(0,3,"Container Size");
                xlsWriteLabel(0,4,"Shipper Name");
				xlsWriteLabel(0,5,"Pick Up Address");
				xlsWriteLabel(0,6,"Special Instruction");
				xlsWriteLabel(0,7,"Plate#");
				
                $xlsRow = 1;
                while($row=mysql_fetch_array($result)) 
				{
				$query_trip = "SELECT * FROM truck WHERE id='".$row['status_tid']."'";
				$result_trip = mysql_query($query_trip) or die(mysql_error());
				$row_trip = mysql_fetch_array($result_trip);
				
				$query_trucker = "SELECT * FROM trucker WHERE id='".$row['trucker_id']."'";
				$result_trucker = mysql_query($query_trucker) or die(mysql_error());
				$row_trucker = mysql_fetch_array($result_trucker);
				
				$query_release = "SELECT * FROM status_log WHERE trip_detail_id='".$row['trid']."' AND status='pickup'";
				$result_release = mysql_query($query_release) or die(mysql_error());
				$row_release = mysql_fetch_array($result_release);
				
				if($row['statlog']=='pickup')
				$mtrelease = $row['update_date'];
				
				if($row['statlog']!='pickup')
				$mtrelease = '';
				
				
				
				/*$query_stat = "SELECT * FROM status_log WHERE trip_detail_id='".$row['id']."' and status='complete'";
				$result_stat = mysql_query($query_stat) or die("ERROR");
				$row_stat = mysql_fetch_array($result_stat);*/
				
				++$i;
						  xlsWriteLabel($xlsRow,0,$row['booking_source']);
						  xlsWriteLabel($xlsRow,1,$row['booking_number']);
						  xlsWriteLabel($xlsRow,2,$row['quantity']);
						  xlsWriteLabel($xlsRow,3,$row['container_size']);
						  xlsWriteLabel($xlsRow,4,$row['customer']);
						  xlsWriteLabel($xlsRow,5,$row['pick_address']);
						  xlsWriteLabel($xlsRow,6,$row['instruction']);
                          xlsWriteLabel($xlsRow,7,$row_trip['plate']);
                    $xlsRow++;
                    
                }
                     xlsEOF();
                 exit();
				}
				
	//STATUS CANCELLED
	
			if($stat=='cancelled')
				{
				xlsBOF();
				xlsWriteLabel(0,0,"Booking Number");
				xlsWriteLabel(0,1,"Shipper Code");
                xlsWriteLabel(0,2,"Shipper Name");
				xlsWriteLabel(0,3,"Quantity");
				xlsWriteLabel(0,4,"Container Size");
				xlsWriteLabel(0,5,"Type");
				xlsWriteLabel(0,6,"Commodity Class");
				xlsWriteLabel(0,7,"Trucker Name");
				xlsWriteLabel(0,8,"Pick Up Datetime");
				//xlsWriteLabel(0,9,"MT Release Datetime");
				xlsWriteLabel(0,9,"Pick Up Address");
				xlsWriteLabel(0,10,"Contact Person");
				xlsWriteLabel(0,11,"Contact Number");
				xlsWriteLabel(0,12,"Special Instruction");
				xlsWriteLabel(0,13,"Vessel Voyage");
				xlsWriteLabel(0,14,"Vessel Voyage ETD");
                xlsWriteLabel(0,15,"TAS#");
				xlsWriteLabel(0,16,"TAS Status");
				//xlsWriteLabel(0,18,"Completion Date");
				xlsWriteLabel(0,17,"Plate#");
				
                $xlsRow = 1;
                while($row=mysql_fetch_array($result)) 
				{
				$query_trip = "SELECT * FROM truck WHERE id='".$row['truck_id']."'";
				$result_trip = mysql_query($query_trip) or die(mysql_error());
				$row_trip = mysql_fetch_array($result_trip);
				
				$query_trucker = "SELECT * FROM trucker WHERE id='".$row['trucker_id']."'";
				$result_trucker = mysql_query($query_trucker) or die(mysql_error());
				$row_trucker = mysql_fetch_array($result_trucker);
				
				/*$query_release = "SELECT * FROM status_log WHERE trip_detail_id='".$row['trid']."' AND status='pickup'";
				$result_release = mysql_query($query_release) or die(mysql_error());
				$row_release = mysql_fetch_array($result_release);
				
				if($row['statlog']=='pickup')
				$mtrelease = $row['update_date'];
				
				if($row['statlog']!='pickup')
				$mtrelease = '';*/
				
				
				
				/*$query_stat = "SELECT * FROM status_log WHERE trip_detail_id='".$row['id']."' and status='complete'";
				$result_stat = mysql_query($query_stat) or die("ERROR");
				$row_stat = mysql_fetch_array($result_stat);*/
				
				++$i;
						  xlsWriteLabel($xlsRow,0,$row['booking_number']);
						  xlsWriteLabel($xlsRow,1,$row['shipper_code']);
						  xlsWriteLabel($xlsRow,2,$row['customer']);
						  xlsWriteLabel($xlsRow,3,$row['quantity']);
						  xlsWriteLabel($xlsRow,4,$row['container_size']);
                          xlsWriteLabel($xlsRow,5,$row['type']);
                          xlsWriteLabel($xlsRow,6,$row['commodity_type']);
						  xlsWriteLabel($xlsRow,7,$row_trucker['name']);
						  xlsWriteLabel($xlsRow,8,$row['positioning_date'].''.$row['positioning_time']);
						  //xlsWriteLabel($xlsRow,9,$row_release['update_date']);
						  xlsWriteLabel($xlsRow,9,$row['pick_address']);
						  xlsWriteLabel($xlsRow,10,$row['contact_person']);
						  xlsWriteLabel($xlsRow,11,$row['contact_number']);
						  xlsWriteLabel($xlsRow,12,$row['instruction']);
						  xlsWriteLabel($xlsRow,13,$row['vessel_voyage']);
						  xlsWriteLabel($xlsRow,14,$row['vessel_voyage_etd']);
                          xlsWriteLabel($xlsRow,15,$row['tas_number']);
						  xlsWriteLabel($xlsRow,16,$row['status']);
						  //xlsWriteLabel($xlsRow,18,$row['update_date']);
                          xlsWriteLabel($xlsRow,17,$row_trip['plate']);
                    $xlsRow++;
                    
                }
                     xlsEOF();
                 exit();
				}

                
?>